SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE PROCEDURE [Live].[Prop3Q13_PicardTable]
 AS
       BEGIN
             DECLARE @dbname  VARCHAR(50)
             DECLARE @FundID  VARCHAR(10)
             DECLARE @NAVDate DATETIME
             
             
             
             SET @FundID           = 'FSIC'
             SET @NAVDate          = '11/18/2013'
             DECLARE @ExportId INT = (SELECT MAX(e.ExportId)
              FROM FSFundModel.Reporting.Export e
             WHERE e.FundId  = @FundID
               AND e.NAVDate = @NAVDate)
            SELECT i.InstrumentId
					, ah.*
                 , fci.FundCreditTransType
                 , iss.IssuerName
                 , ah.InstrName
                 , ah.InstrClass
                 , p.PortfolioName
                 , act.ActivityType
                 , t.TradeAmount
                 , act.TradePrice
                 , act.ActivityDate
                 , prevDate.GAAPYldPurchPrc AS 'TD-1GAAPYieldAtPurch'
                 , CASE WHEN ah.PropReason IS NOT NULL
                         THEN 1
                         ELSE 0
                   END AS IsProprietary
                 , ah.PropReason
                 , ah.PropTrack
                 , a.TotalCredit
                 , iss.TTMEBITDA
                 , i.MaturityDate
                 
                 , prevDate.AmortCost 'AmortizedCostBeforeTrade'
                 , t.AmortCost 'AmortCostTrade'
                 , a.IsActiveEndOfDay
                 , CASE when a.TotalCredit IS NOT NULL
                         THEN 1
                         ELSE 0
                   END AS IsUnfunded
                 , a.AvailCredit
                 ,a.GAAPYldPurchPrc
                 , a.AmortCost
                 , prevDate.AmortCostPctPar 'TD-1AmortCostPctPar'
              
              FROM FSFundModel.Reporting.Asset a
			      LEFT  JOIN FSFundModel.Reporting.Trade t
                            ON         a.ExportId     = t.ExportId
                            AND        a.InstrumentId = t.InstrumentId
                            AND        a.LotId        = t.LotId
                            AND        a.PortfolioId  = t.PortfolioId
                   
                   LEFT JOIN  FSFundModel.Reporting.ActivitySummary act
                   ON act.ExportId = a.ExportId
                   AND act.ActivitySummaryId = t.ActivitySummaryId
              LEFT JOIN ReportingScripts.live.AssetHelper(@ExportId) ah
                      ON ah.AssetIntrnlId = a.AssetIntrnlId
              INNER JOIN FSFundModel.Reporting.Instrument i
                      ON i.ExportId     = a.ExportId
                     AND a.InstrumentId = i.InstrumentId
              
              INNER JOIN FSFundModel.Reporting.Issuer iss
                      ON iss.ExportId = a.ExportId
                     AND i.IssuerId   = iss.IssuerId
              
              INNER JOIN FSFundModel.Reporting.InstrumentDefinition id
                      ON i.InstDefId = id.InstDefId
                     AND id.ExportId = a.ExportId
              
              INNER JOIN FSFundModel.Reporting.FundIssuer fi
                      ON a.ExportId  = fi.ExportId
                     AND fi.IssuerId = iss.IssuerId
              
              INNER JOIN FSFundModel.Reporting.Portfolio p
                      ON a.ExportId    = p.ExportId
                     AND a.PortfolioId = p.PortfolioId
              
              INNER JOIN FSFundModel.Reporting.Fund f
                      ON a.ExportId = f.ExportId
                   
                   
                        
             
             OUTER APPLY( SELECT ft.*
                         
                          
                          FROM FSFundModel.Reporting.FundCreditTrans ft
                         
                         WHERE ft.ExportId    = a.ExportId
                           AND ft.PortfolioId = a.PortfolioId
						   AND ft.InstrumentId =  a.InstrumentId
						   AND ft.LotId = a.LotId)          fci
                   
                   OUTER  APPLY (
                    SELECT pd.*  FROM live.getAssetByNAVDateOffset(act.ActivityDate, -1, a.PortfolioId,a.InstrumentId,a.LotId) pd --(f.PrevNAVDate, @FundID) pd
                     ) prevDate
             WHERE a.ExportId = @ExportId
               
               AND act.ActivityDate > '6/28/2013'
          
          
          ORDER BY id.SortOrder
             --    , act.ActivityDate ASC
       
       
       
       
       
       
       END
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Live', 'SCHEMA', N'Live', 'PROCEDURE', N'Prop3Q13_PicardTable', NULL, NULL
GO
